﻿using System;
using System.Collections.Generic;
using System.Linq;
using SenerPro;
using System.Web;

namespace MensUnoShop.Models
{
    public class DBMember
    {
        private DataClassesMenDataContext _dc;
        private Logger _log = new Logger(Config.logpath);
        public bool AddNewMember(string email, string password, string firstname, string lastname, string telh, string telo, string mobile, bool ismarried, bool sex, string birth, string edu, string job, string income, string country, string zip, string city, string area, string address,bool isedm, ref string mguid)
        {
            mguid = string.Empty;
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.UMember.Where(mm => mm.Memail == email);
                    if (me.Count() > 0)
                        ischeck = false;
                    else
                    {
                        UMember m = new UMember();
                        m.Mguid = Guid.NewGuid();
                        m.Memail = email;
                        m.MKpass = password;
                        m.Msignup = DateTime.Now;
                        m.Mstatus = false;
                        _dc.UMember.InsertOnSubmit(m);
                        _dc.SubmitChanges();
                        UMemberData md = new UMemberData();
                        md.MDMguid = m.Mguid;
                        md.MDfirstname = firstname;
                        md.MDlastname = lastname;
                        md.MDbirth = birth;
                        md.MDedu = edu;
                        md.MDsex = sex;
                        md.MDtelh = telh;
                        md.MDtelo = telo;
                        md.MDzip = zip;
                        md.MDcountry = country;
                        md.MDcity = city;
                        md.MDarea = area;
                        md.MDaddress = address;
                        md.MDincome = income;
                        md.MDismarried = ismarried;
                        md.MDjob = job;
                        md.MDmobile = mobile;
                        md.MDisedm = isedm;
                        _dc.UMemberData.InsertOnSubmit(md);
                        _dc.SubmitChanges();
                        mguid = m.Mguid.ToString("N");
                        ischeck = true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "AddNewMember", ex.ToString());
            }

            return ischeck;
        }

        public bool StartNewMember(Guid mguid, ref string msg)
        {
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var m = _dc.UMember.SingleOrDefault(mm => mm.Mguid == mguid && mm.Msignup != DateTime.Parse("1982/12/8"));
                    if (m != null)
                    {
                        if (m.Mstatus)
                            msg = "帳號已啟用!!";
                        else
                        {
                            m.Mstatus = true;
                            _dc.SubmitChanges();
                            msg = "帳號啟用成功!!請重新登入";
                        }
                        ischeck = true;
                    }
                    else
                        msg = "帳號不存在!!";
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "StartNewMember", ex.ToString());
            }

            return ischeck;
        }

        public bool CheckMemberLogin(string email, string pwd, ref string err)
        {
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var m = _dc.UMember.SingleOrDefault(mm => mm.Memail == email && mm.MKpass==pwd);
                    if (m != null)
                    {
                        if (m.Mstatus)
                            ischeck = true;
                        else if (m.Msignup == DateTime.Parse("1982/12/8"))
                            err = "帳號異常，請連絡客服!";
                        else
                            err = "您的帳號尚未開通!";
                    }
                    else
                        err = "帳號密碼錯誤!!!";
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "CheckMemberLogin", ex.ToString());
            }

            return ischeck;
        }

        public Member GetMemberData(string email)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var m = _dc.UMember.SingleOrDefault(mm => mm.Memail == email);
                    if (m != null)
                    {
                        var md = m.UMemberData.FirstOrDefault(mmd=>mmd.MDMguid==m.Mguid);
                        if (md != null)
                        {
                            Member mb = new Member(email);
                            mb.MGUID = m.Mguid;
                            mb.SIGNUP = m.Msignup;
                            mb.ADDRESS = md.MDaddress;
                            mb.AREA = md.MDarea;
                            mb.BIRTH = md.MDbirth;
                            mb.CITY = md.MDcity;
                            mb.COUNTRY = md.MDcountry;
                            mb.EDU = md.MDedu;
                            mb.FIRSTNAME = md.MDfirstname;
                            mb.INCOME = md.MDincome;
                            mb.ISMARRIED = md.MDismarried;
                            mb.JOB = md.MDjob;
                            mb.LASTNAME = md.MDlastname;
                            mb.MOBILE = md.MDmobile;
                            mb.SEX = md.MDsex;
                            mb.TELH = md.MDtelh;
                            mb.TELO = md.MDtelo;
                            mb.ZIP = md.MDzip;
                            mb.STATUS = m.Mstatus;
                            return mb;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMemberData", ex.ToString());
            }
            return new Member();
        }

        public void UptMemberLastLogin(Guid _mguid, string ip)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.UMemberLogin.InsertOnSubmit(new UMemberLogin() { MLip = ip, MLdatein = DateTime.Now, MLMguid = _mguid });
                    _dc.SubmitChanges();
                }

            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "UptMemberLastLogin", ex.ToString());
            }
        }

        public void UptMemberData(Member m)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.SP_UptUMemberData(m.MGUID, m.FIRSTNAME, m.LASTNAME, m.SEX, m.EDU, m.JOB, m.INCOME, m.BIRTH, m.TELO, m.TELH, m.MOBILE, m.COUNTRY, m.ZIP, m.CITY, m.AREA, m.ADDRESS, m.ISMARRIED, m.ISEDM);
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "UptMemberData", ex.ToString());
            }
        }

        public void UptMember(Guid _mguid, string email, string kpass, bool status)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.SP_UptUMember(_mguid, email, kpass, status);
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "UptMember", ex.ToString());
            }
        }

        public void SetMemberOff(Guid mguid) 
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var m = _dc.UMember.SingleOrDefault(mm => mm.Mguid == mguid);
                    if (m != null)
                    {
                        m.Mstatus = false;
                        m.Msignup = DateTime.Parse("1982/12/8");
                        _dc.SubmitChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetMemberOff", ex.ToString());
            }
        }
        public void SetMemberOn(Guid mguid) 
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var m = _dc.UMember.SingleOrDefault(mm => mm.Mguid == mguid);
                    if (m != null)
                    {
                        m.Mstatus = true;
                        m.Msignup = DateTime.Now;
                        _dc.SubmitChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetMemberOn", ex.ToString());
            }
        }

        public UMember GetMember(string email)
        {
            UMember m = new UMember();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.UMember.Where(mm => mm.Memail == email).SingleOrDefault();
                    if (me != null)
                    {
                        
                        m.Mguid = me.Mguid;
                        m.Memail = me.Memail;
                        m.MKpass = me.MKpass;
                        m.Msignup = me.Msignup;
                        m.Mstatus = me.Mstatus;
                        m.MKpass = me.MKpass;
                        m.Mstatus = me.Mstatus;
                    }                    
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMember", ex.ToString());
            }

            return m;
        }
        public Member GetMember(Guid mguid)
        {
            Member m = new Member();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.V_MemberList.Where(vml => vml.Mguid == mguid).SingleOrDefault();

                    if (me != null)
                    {
                        m.MGUID = me.Mguid;
                        m.EMAIL = me.Memail;
                        m.PASSWORD = me.MKpass;
                        m.ZIP = me.MDzip;
                        m.COUNTRY = me.MDcountry;
                        m.CITY = me.MDcity;
                        m.AREA = me.MDarea;
                        m.ADDRESS = me.MDaddress;
                        m.FIRSTNAME = me.MDfirstname;
                        m.LASTNAME = me.MDlastname;
                        m.MOBILE = me.MDmobile;
                        m.SEX = me.MDsex;
                        m.TELH = me.MDtelh;
                        m.TELO = me.MDtelo;
                        m.BIRTH = me.MDbirth;
                        m.EDU = me.MDedu;
                        m.INCOME = me.MDincome;
                        m.ISMARRIED = me.MDismarried;
                        m.JOB = me.MDjob;
                        m.STATUS = me.Mstatus;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMember", ex.ToString());
            }
            return m;
        }

        public UMemberData GetMemberData(Guid mguid)
        {
            UMemberData md = new UMemberData();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.UMemberData.Where(umd => umd.MDMguid == mguid).SingleOrDefault();
                    if (me != null)
                    {
                        md.MDid = me.MDid;
                        md.MDMguid = me.MDMguid;
                        md.MDfirstname = me.MDfirstname;
                        md.MDlastname = me.MDlastname;
                        md.MDbirth = me.MDbirth;
                        md.MDedu = me.MDedu;

                        md.MDcountry = me.MDcountry;
                        md.MDzip = me.MDzip;
                        md.MDcity = me.MDcity;
                        md.MDarea = me.MDarea;
                        md.MDaddress = me.MDaddress;
                        
                        md.MDincome = me.MDincome;
                        md.MDismarried = me.MDismarried;
                        md.MDjob = me.MDjob;
                        md.MDmobile = me.MDmobile;
                        md.MDsex = me.MDsex;
                        md.MDtelh = me.MDtelh;
                        md.MDtelo = me.MDtelo;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMemberData", ex.ToString());
            }

            return md;
        }

        public List<UMemberFavorite> GetMemberFavorite(Guid _mguid)
        {
            List<UMemberFavorite> umfList = new List<UMemberFavorite>();            
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.UMemberFavorite.Where(mf => mf.MFmguid == _mguid);
                    if (me != null)
                    {
                        foreach (var item in me)
                        {
                            UMemberFavorite umf = new UMemberFavorite();
                            umf.MFid = item.MFid;
                            umf.MFmguid = item.MFmguid;
                            umf.MFmkguid = item.MFmkguid;
                            umf.MFproductid = item.MFproductid;
                            umf.MFspecid = item.MFspecid;                           
                            umf.MFdatein = item.MFdatein;
                            umf.MFmktype = item.MFmktype;
                            umfList.Add(umf);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMemberFavorite", ex.ToString());
            }

            return umfList;
        }

        public bool DelMemberFavorite(Guid _mfid)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.UMemberFavorite.DeleteAllOnSubmit(_dc.UMemberFavorite.Where(umf => umf.MFid == _mfid));
                    _dc.SubmitChanges();
                    return true;
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "DelMemberFavorite", ex.ToString());
                return false;
            }

        }

        public bool AddMemberFavorite(Guid _mfmguid, Guid _mfmkguid, Guid _mfproductid, Guid _mfspecid)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var umf = _dc.UMemberFavorite.FirstOrDefault(f => f.MFmkguid == _mfmkguid && f.MFmguid == _mfmguid && f.MFproductid == _mfproductid && f.MFspecid == _mfspecid);
                    if (umf != null)
                        return true;
                    else
                    {
                        umf = new UMemberFavorite();
                        umf.MFid = Guid.NewGuid();
                        umf.MFmguid = _mfmguid;
                        umf.MFmkguid = _mfmkguid;
                        umf.MFproductid = _mfproductid;
                        umf.MFspecid = _mfspecid;
                        umf.MFdatein = DateTime.Now;
                        umf.MFmktype = _dc.UMarket.Single(m => m.MKguid == _mfmkguid).MKtype;
                        _dc.UMemberFavorite.InsertOnSubmit(umf);
                        _dc.SubmitChanges();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "AddMemberFavorite", ex.ToString());
                return false;
            }

        }

        public List<Member> GetMemberList(string querychoice, string querycontent)
        {
            List<Member> memberList = new List<Member>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var me = _dc.V_MemberList.AsQueryable();

                    switch (querychoice)
                    {             
                        case "queryall":
                            break;
                        case "queryemail":
                            me = me.Where(vml => vml.Memail == querycontent);
                            break;
                        case "querymname":
                            me = me.Where(vml => (vml.MDlastname + vml.MDfirstname).Trim() == querycontent);
                            break;
                        default:
                            break;
                    }                    
                    
                    if (me != null)
                    {
                        foreach (var item in me)
                        {
                            Member m = new Member();
                            m.MGUID = item.Mguid;
                            m.EMAIL = item.Memail;
                            m.PASSWORD = item.MKpass;
                            m.ZIP = item.MDzip;
                            m.COUNTRY = item.MDcountry;
                            m.CITY = item.MDcity;
                            m.AREA = item.MDarea;
                            m.ADDRESS = item.MDaddress;
                            m.FIRSTNAME = item.MDfirstname;
                            m.LASTNAME = item.MDlastname;
                            m.MOBILE = item.MDmobile;
                            m.SEX = item.MDsex;
                            m.TELH = item.MDtelh;
                            m.TELO = item.MDtelo;
                            m.BIRTH = item.MDbirth;
                            m.EDU = item.MDedu;
                            m.INCOME = item.MDincome;
                            m.ISMARRIED = item.MDismarried;
                            m.JOB = item.MDjob;
                            m.STATUS = item.Mstatus;
                            memberList.Add(m);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMember", ex.ToString());
            }
            return memberList;
        }

        

        public List<Coupon> GetMemberCouponList(Guid mguid)
        {
            List<Coupon> clist = new List<Coupon>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var cc = _dc.USaleskitCoupon.Where(sc => sc.UMemberCoupon.SingleOrDefault().MCmguid == mguid && sc.SCislog);
                    foreach (var c in cc)
                    {
                        Coupon cp = new Coupon();
                        cp.DATEIN = c.UMemberCoupon.SingleOrDefault().MCdatein;
                        cp.ISUSED = c.UMemberCoupon.SingleOrDefault().MCisused;
                        cp.MCID = c.UMemberCoupon.SingleOrDefault().MCid;
                        cp.PRICE = c.SCprice;
                        cp.TITLE = c.SCtitle;
                        cp.USEDTIME = c.UMemberCoupon.SingleOrDefault().MCusetime;
                        clist.Add(cp);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetMemberCouponList", ex.ToString());
            }
            return clist;
        }

        public bool SetCoupon(Guid mguid, string ticket)
        {
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    //檢查是否有用過/存在
                    var c = _dc.USaleskitCoupon.FirstOrDefault(cc => cc.SCticket == ticket && !cc.SCislog);
                    if (c != null)
                    {
                        UMemberCoupon mc = new UMemberCoupon();
                        mc.MCdatein = DateTime.Now;
                        mc.MCisused = false;
                        mc.MCmguid = mguid;
                        mc.MCscid = c.SCid;
                        _dc.UMemberCoupon.InsertOnSubmit(mc);
                        c.SCislog = true;
                        _dc.SubmitChanges();
                        ischeck = true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetCoupon", ex.ToString());
            }
            return ischeck;
        }

        public bool SetCoupon(Guid mguid, string ticket, ref int mcid)
        {
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    //檢查是否有用過/存在
                    var c = _dc.USaleskitCoupon.FirstOrDefault(cc => cc.SCticket == ticket && !cc.SCislog);
                    if (c != null)
                    {
                        UMemberCoupon mc = new UMemberCoupon();
                        mc.MCdatein = DateTime.Now;
                        mc.MCisused = false;
                        mc.MCmguid = mguid;
                        mc.MCscid = c.SCid;
                        _dc.UMemberCoupon.InsertOnSubmit(mc);
                        c.SCislog = true;
                        _dc.SubmitChanges();
                        ischeck = true;
                        mcid = mc.MCid;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetCoupon", ex.ToString());
            }
            return ischeck;
        }

        public bool SetCouponUsed(int mcid, string txid)
        {
            bool ischeck = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var c = _dc.UMemberCoupon.FirstOrDefault(cc => cc.MCid==mcid && !cc.MCisused);
                    if (c != null)
                    {
                        c.MCisused = true;
                        c.MCusetime = DateTime.Now;
                        c.MCtxid = txid;
                        _dc.SubmitChanges();
                        ischeck = true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetCouponUsed", ex.ToString());
            }
            return ischeck;
        }

        //將登錄回復, 綁定刪除。
        public void SetCouponRevert(int mcid, string txid)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var mc = _dc.UMemberCoupon.FirstOrDefault(cc => cc.MCid == mcid && cc.MCisused && cc.MCtxid==txid);
                    if (mc != null)
                    {
                        var c = _dc.USaleskitCoupon.SingleOrDefault(sc => sc.SCid == mc.MCscid);
                        if (c != null)
                        {
                            c.SCislog = false;
                            _dc.SubmitChanges();
                        }
                        _dc.UMemberCoupon.DeleteOnSubmit(mc);
                        _dc.SubmitChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetCouponRevert", ex.ToString());
            }
        }


        //因為不綁定，所以取消的時候也要把會員綁定給刪除
        public void ResetCoupon(int mcid)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var mc = _dc.UMemberCoupon.FirstOrDefault(cc => cc.MCid == mcid);
                    if (mc != null)
                    {
                        var c = _dc.USaleskitCoupon.SingleOrDefault(sc => sc.SCid == mc.MCscid);
                        if (c != null)
                        {
                            c.SCislog = false;
                            _dc.SubmitChanges();
                        }
                        _dc.UMemberCoupon.DeleteOnSubmit(mc);
                        _dc.SubmitChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "ResetCoupon", ex.ToString());
            }
        }

        public bool SetEdm(string email)
        {
            bool ischk = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    EDM_Member ed = _dc.EDM_Member.SingleOrDefault(e => e.Email == email);
                    if (ed == null)
                    {
                        ed = new EDM_Member();
                        ed.Email = email;
                        ed.CreateDate = DateTime.Now;
                        _dc.EDM_Member.InsertOnSubmit(ed);
                        _dc.SubmitChanges();
                        ischk = true;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "SetEdm", ex.ToString());
            }
            return ischk;
        }
    }
}